/**
 * Database 模块 API
 * 数据库相关接口
 */

import { request, type ApiResponse, type RequestParamValue } from "../request";

// ============ 类型定义 ============

// -------- SQL Snippets --------

/**
 * SQL 片段可见性
 */
export type SnippetVisibility = "user" | "project" | "org" | "public";

/**
 * SQL 片段项目信息
 */
export interface SnippetProject {
	id: number;
	name: string;
}

/**
 * SQL 片段用户信息
 */
export interface SnippetUser {
	id: number;
	username: string;
}

/**
 * SQL 片段（列表项）
 */
export interface SnippetListItem {
	id: string;
	inserted_at: string;
	updated_at: string;
	type: "sql";
	visibility: SnippetVisibility;
	name: string;
	description: string | null;
	project: SnippetProject;
	owner: SnippetUser;
	updated_by: SnippetUser;
	favorite: boolean;
}

/**
 * SQL 片段列表响应
 */
export interface SnippetList {
	data: SnippetListItem[];
	cursor?: string;
}

/**
 * SQL 片段内容
 */
export interface SnippetContent {
	favorite?: boolean;
	schema_version: string;
	sql: string;
}

/**
 * SQL 片段详情
 */
export interface Snippet extends SnippetListItem {
	content: SnippetContent;
}

// -------- SSL Enforcement --------

/**
 * SSL 强制配置
 */
export interface SslEnforcementConfig {
	database: boolean;
}

/**
 * SSL 强制响应
 */
export interface SslEnforcementResponse {
	currentConfig: SslEnforcementConfig;
	appliedSuccessfully: boolean;
}

/**
 * SSL 强制请求
 */
export interface SslEnforcementRequest {
	requestedConfig: SslEnforcementConfig;
}

// -------- TypeScript Types --------

/**
 * TypeScript 类型响应
 */
export interface TypescriptResponse {
	types: string;
}

// -------- Readonly Mode --------

/**
 * 只读模式状态
 */
export interface ReadOnlyStatus {
	enabled: boolean;
	override_enabled: boolean;
	override_active_until: string;
}

// -------- Read Replicas --------

/**
 * 读副本设置请求
 */
export interface SetUpReadReplicaBody {
	read_replica_region: string;
}

/**
 * 移除读副本请求
 */
export interface RemoveReadReplicaBody {
	database_identifier: string;
}

// -------- Postgres Config --------

/**
 * 会话复制角色
 */
export type SessionReplicationRole = "origin" | "replica" | "local";

/**
 * Postgres 配置响应
 */
export interface PostgresConfigResponse {
	effective_cache_size?: string;
	logical_decoding_work_mem?: string;
	maintenance_work_mem?: string;
	track_activity_query_size?: string;
	max_connections?: number;
	max_locks_per_transaction?: number;
	max_parallel_maintenance_workers?: number;
	max_parallel_workers?: number;
	max_parallel_workers_per_gather?: number;
	max_replication_slots?: number;
	max_slot_wal_keep_size?: string;
	max_standby_archive_delay?: string;
	max_standby_streaming_delay?: string;
	max_wal_size?: string;
	max_wal_senders?: number;
	max_worker_processes?: number;
	session_replication_role?: SessionReplicationRole;
	shared_buffers?: string;
	statement_timeout?: string;
	track_commit_timestamp?: boolean;
	wal_keep_size?: string;
	wal_sender_timeout?: string;
	work_mem?: string;
	checkpoint_timeout?: string;
	hot_standby_feedback?: boolean;
}

/**
 * 更新 Postgres 配置请求
 */
export interface UpdatePostgresConfigBody extends PostgresConfigResponse {
	restart_database?: boolean;
}

// -------- PgBouncer Config --------

/**
 * 连接池模式
 */
export type PoolMode = "transaction" | "session" | "statement";

/**
 * PgBouncer 配置响应
 */
export interface PgbouncerConfigResponse {
	default_pool_size?: number;
	ignore_startup_parameters?: string;
	max_client_conn?: number;
	pool_mode?: PoolMode;
	connection_string?: string;
	server_idle_timeout?: number;
	server_lifetime?: number;
	query_wait_timeout?: number;
	reserve_pool_size?: number;
}

// -------- Pooler (Supavisor) Config --------

/**
 * 数据库类型
 */
export type DatabaseType = "PRIMARY" | "READ_REPLICA";

/**
 * Supavisor 连接池模式
 */
export type SupavisorPoolMode = "transaction" | "session";

/**
 * Supavisor 配置响应
 */
export interface SupavisorConfigResponse {
	identifier: string;
	database_type: DatabaseType;
	is_using_scram_auth: boolean;
	db_user: string;
	db_host: string;
	db_port: number;
	db_name: string;
	connection_string: string;
	connectionString: string;
	default_pool_size: number | null;
	max_client_conn: number | null;
	pool_mode: SupavisorPoolMode;
}

/**
 * 更新 Supavisor 配置请求
 */
export interface UpdateSupavisorConfigBody {
	default_pool_size?: number;
	pool_mode?: SupavisorPoolMode;
}

/**
 * 更新 Supavisor 配置响应
 */
export interface UpdateSupavisorConfigResponse {
	default_pool_size: number | null;
	pool_mode: string;
}

// -------- CLI Login Role --------

/**
 * 创建登录角色请求
 */
export interface CreateRoleBody {
	read_only?: boolean;
}

/**
 * 创建登录角色响应
 */
export interface CreateRoleResponse {
	role: string;
	password: string;
	ttl_seconds: number;
}

/**
 * 删除角色响应
 */
export interface DeleteRolesResponse {
	message: "ok";
}

// -------- Migrations --------

/**
 * 迁移列表项
 */
export interface MigrationListItem {
	version: string;
	name?: string;
}

/**
 * 迁移详情
 */
export interface MigrationDetail {
	version: string;
	name?: string;
	statements?: string[];
	rollback?: string[];
	created_by?: string;
	idempotency_key?: string;
}

/**
 * 创建迁移请求
 */
export interface CreateMigrationBody {
	query: string;
	name?: string;
	rollback?: string;
}

/**
 * 更新迁移请求
 */
export interface UpsertMigrationBody {
	query: string;
	name?: string;
	rollback?: string;
}

/**
 * 修改迁移请求
 */
export interface PatchMigrationBody {
	name?: string;
	rollback?: string;
}

// -------- Query --------

/**
 * 执行查询请求
 */
export interface RunQueryBody {
	query: string;
	parameters?: unknown[];
	read_only?: boolean;
}

/**
 * 只读查询请求
 */
export interface ReadOnlyQueryBody {
	query: string;
	parameters?: unknown[];
}

// -------- Database Context --------

/**
 * Schema 信息
 */
export interface SchemaInfo {
	name: string;
}

/**
 * 数据库信息
 */
export interface DatabaseInfo {
	name: string;
	schemas: SchemaInfo[];
}

/**
 * 数据库元数据响应
 */
export interface DatabaseContextResponse {
	databases: DatabaseInfo[];
}

// -------- Password --------

/**
 * 更新密码请求
 */
export interface UpdatePasswordBody {
	password: string;
}

/**
 * 更新密码响应
 */
export interface UpdatePasswordResponse {
	message: string;
}

// -------- JIT Access --------

/**
 * CIDR 项
 */
export interface CidrItem {
	cidr: string;
}

/**
 * 允许的网络
 */
export interface AllowedNetworks {
	allowed_cidrs?: CidrItem[];
	allowed_cidrs_v6?: CidrItem[];
}

/**
 * 用户角色
 */
export interface UserRole {
	role: string;
	expires_at?: number;
	allowed_networks?: AllowedNetworks;
}

/**
 * JIT 访问响应
 */
export interface JitAccessResponse {
	user_id: string;
	user_roles: UserRole[];
}

/**
 * 授权 JIT 访问请求
 */
export interface AuthorizeJitAccessBody {
	role: string;
	rhost?: string;
}

/**
 * 授权 JIT 访问响应
 */
export interface JitAuthorizeAccessResponse {
	user_id: string;
	user_role: UserRole;
}

/**
 * 更新 JIT 访问请求
 */
export interface UpdateJitAccessBody {
	user_id: string;
	roles: UserRole[];
}

/**
 * JIT 访问列表响应
 */
export interface JitListAccessResponse {
	items: JitAccessResponse[];
}

// -------- Backups --------

/**
 * 备份状态
 */
export type BackupStatus = "COMPLETED" | "FAILED" | "PENDING" | "REMOVED" | "ARCHIVED" | "CANCELLED";

/**
 * 备份项
 */
export interface BackupItem {
	is_physical_backup: boolean;
	status: BackupStatus;
	inserted_at: string;
}

/**
 * 物理备份数据
 */
export interface PhysicalBackupData {
	earliest_physical_backup_date_unix?: number;
	latest_physical_backup_date_unix?: number;
}

/**
 * 备份列表响应
 */
export interface BackupsResponse {
	region: string;
	walg_enabled: boolean;
	pitr_enabled: boolean;
	backups: BackupItem[];
	physical_backup_data: PhysicalBackupData;
}

/**
 * PITR 恢复请求
 */
export interface RestorePitrBody {
	recovery_time_target_unix: number;
}

/**
 * 还原点状态
 */
export type RestorePointStatus = "AVAILABLE" | "PENDING" | "REMOVED" | "FAILED";

/**
 * 创建还原点请求
 */
export interface RestorePointPostBody {
	name: string;
}

/**
 * 还原点响应
 */
export interface RestorePointResponse {
	name: string;
	status: RestorePointStatus;
}

/**
 * 撤销到还原点请求
 */
export interface UndoBody {
	name: string;
}

// ============ API 方法 ============

// -------- SQL Snippets --------

/**
 * 获取 SQL 片段列表
 */
export function listSnippets(params?: {
	project_ref?: string;
	cursor?: string;
	limit?: string;
	sort_by?: "name" | "inserted_at";
	sort_order?: "asc" | "desc";
}): Promise<ApiResponse<SnippetList>> {
	return request.get<SnippetList>("/v1/snippets", params as Record<string, RequestParamValue>);
}

/**
 * 获取指定 SQL 片段
 */
export function getSnippet(id: string): Promise<ApiResponse<Snippet>> {
	return request.get<Snippet>(`/v1/snippets/${id}`);
}

// -------- SSL Enforcement --------

/**
 * 获取 SSL 强制配置
 */
export function getSslEnforcement(
	ref: string
): Promise<ApiResponse<SslEnforcementResponse>> {
	return request.get<SslEnforcementResponse>(`/v1/projects/${ref}/ssl-enforcement`);
}

/**
 * 更新 SSL 强制配置
 */
export function updateSslEnforcement(
	ref: string,
	data: SslEnforcementRequest
): Promise<ApiResponse<SslEnforcementResponse>> {
	return request.put<SslEnforcementResponse>(
		`/v1/projects/${ref}/ssl-enforcement`,
		data
	);
}

// -------- TypeScript Types --------

/**
 * 生成 TypeScript 类型
 */
export function generateTypescriptTypes(
	ref: string,
	params?: { included_schemas?: string }
): Promise<ApiResponse<TypescriptResponse>> {
	return request.get<TypescriptResponse>(
		`/v1/projects/${ref}/types/typescript`,
		params as Record<string, RequestParamValue>
	);
}

// -------- Readonly Mode --------

/**
 * 获取只读模式状态
 */
export function getReadOnlyStatus(ref: string): Promise<ApiResponse<ReadOnlyStatus>> {
	return request.get<ReadOnlyStatus>(`/v1/projects/${ref}/readonly`);
}

/**
 * 临时禁用只读模式（15分钟）
 */
export function disableReadOnlyTemporarily(
	ref: string
): Promise<ApiResponse<Record<string, never>>> {
	return request.post<Record<string, never>>(
		`/v1/projects/${ref}/readonly/temporary-disable`
	);
}

// -------- Read Replicas --------

/**
 * 设置读副本
 */
export function setupReadReplica(
	ref: string,
	data: SetUpReadReplicaBody
): Promise<ApiResponse<Record<string, never>>> {
	return request.post<Record<string, never>>(
		`/v1/projects/${ref}/read-replicas/setup`,
		data
	);
}

/**
 * 移除读副本
 */
export function removeReadReplica(
	ref: string,
	data: RemoveReadReplicaBody
): Promise<ApiResponse<Record<string, never>>> {
	return request.post<Record<string, never>>(
		`/v1/projects/${ref}/read-replicas/remove`,
		data
	);
}

// -------- Postgres Config --------

/**
 * 获取 Postgres 配置
 */
export function getPostgresConfig(
	ref: string
): Promise<ApiResponse<PostgresConfigResponse>> {
	return request.get<PostgresConfigResponse>(`/v1/projects/${ref}/config/database/postgres`);
}

/**
 * 更新 Postgres 配置
 */
export function updatePostgresConfig(
	ref: string,
	data: UpdatePostgresConfigBody
): Promise<ApiResponse<PostgresConfigResponse>> {
	return request.put<PostgresConfigResponse>(
		`/v1/projects/${ref}/config/database/postgres`,
		data
	);
}

// -------- PgBouncer Config --------

/**
 * 获取 PgBouncer 配置
 */
export function getPgbouncerConfig(
	ref: string
): Promise<ApiResponse<PgbouncerConfigResponse>> {
	return request.get<PgbouncerConfigResponse>(`/v1/projects/${ref}/config/database/pgbouncer`);
}

// -------- Pooler (Supavisor) Config --------

/**
 * 获取 Supavisor 连接池配置
 */
export function getPoolerConfig(
	ref: string
): Promise<ApiResponse<SupavisorConfigResponse[]>> {
	return request.get<SupavisorConfigResponse[]>(`/v1/projects/${ref}/config/database/pooler`);
}

/**
 * 更新 Supavisor 连接池配置
 */
export function updatePoolerConfig(
	ref: string,
	data: UpdateSupavisorConfigBody
): Promise<ApiResponse<UpdateSupavisorConfigResponse>> {
	return request.patch<UpdateSupavisorConfigResponse>(
		`/v1/projects/${ref}/config/database/pooler`,
		data as Record<string, unknown>
	);
}

// -------- CLI Login Role --------

/**
 * 创建 CLI 登录角色（临时密码）
 */
export function createLoginRole(
	ref: string,
	data?: CreateRoleBody
): Promise<ApiResponse<CreateRoleResponse>> {
	return request.post<CreateRoleResponse>(
		`/v1/projects/${ref}/cli/login-role`,
		data
	);
}

/**
 * 删除 CLI 登录角色
 */
export function deleteLoginRoles(
	ref: string
): Promise<ApiResponse<DeleteRolesResponse>> {
	return request.delete<DeleteRolesResponse>(`/v1/projects/${ref}/cli/login-role`);
}

// -------- Migrations --------

/**
 * 获取迁移历史列表
 */
export function listMigrations(
	ref: string
): Promise<ApiResponse<MigrationListItem[]>> {
	return request.get<MigrationListItem[]>(`/v1/projects/${ref}/database/migrations`);
}

/**
 * 应用数据库迁移
 */
export function applyMigration(
	ref: string,
	data: CreateMigrationBody,
	idempotencyKey?: string
): Promise<ApiResponse<Record<string, never>>> {
	const headers: Record<string, string> = {};
	if (idempotencyKey) {
		headers["Idempotency-Key"] = idempotencyKey;
	}
	return request.post<Record<string, never>>(
		`/v1/projects/${ref}/database/migrations`,
		data
	);
}

/**
 * 更新或插入迁移（不执行）
 */
export function upsertMigration(
	ref: string,
	data: UpsertMigrationBody,
	idempotencyKey?: string
): Promise<ApiResponse<Record<string, never>>> {
	const headers: Record<string, string> = {};
	if (idempotencyKey) {
		headers["Idempotency-Key"] = idempotencyKey;
	}
	return request.put<Record<string, never>>(
		`/v1/projects/${ref}/database/migrations`,
		data
	);
}

/**
 * 回滚数据库迁移
 */
export function rollbackMigrations(
	ref: string,
	gte: string
): Promise<ApiResponse<Record<string, never>>> {
	return request.delete<Record<string, never>>(
		`/v1/projects/${ref}/database/migrations`,
		{ gte }
	);
}

/**
 * 获取指定版本迁移详情
 */
export function getMigration(
	ref: string,
	version: string
): Promise<ApiResponse<MigrationDetail>> {
	return request.get<MigrationDetail>(`/v1/projects/${ref}/database/migrations/${version}`);
}

/**
 * 修改迁移历史记录
 */
export function patchMigration(
	ref: string,
	version: string,
	data: PatchMigrationBody
): Promise<ApiResponse<Record<string, never>>> {
	return request.patch<Record<string, never>>(
		`/v1/projects/${ref}/database/migrations/${version}`,
		data as Record<string, unknown>
	);
}

// -------- Query --------

/**
 * 执行 SQL 查询
 */
export function runQuery(
	ref: string,
	data: RunQueryBody
): Promise<ApiResponse<unknown>> {
	return request.post<unknown>(
		`/v1/projects/${ref}/database/query`,
		data
	);
}

/**
 * 执行只读 SQL 查询
 */
export function runReadOnlyQuery(
	ref: string,
	data: ReadOnlyQueryBody
): Promise<ApiResponse<unknown>> {
	return request.post<unknown>(
		`/v1/projects/${ref}/database/query/read-only`,
		data
	);
}

// -------- Webhooks --------

/**
 * 启用数据库 Webhooks
 */
export function enableDatabaseWebhook(
	ref: string
): Promise<ApiResponse<Record<string, never>>> {
	return request.post<Record<string, never>>(
		`/v1/projects/${ref}/database/webhooks/enable`
	);
}

// -------- Database Context --------

/**
 * 获取数据库元数据（实验性接口）
 */
export function getDatabaseContext(
	ref: string
): Promise<ApiResponse<DatabaseContextResponse>> {
	return request.get<DatabaseContextResponse>(`/v1/projects/${ref}/database/context`);
}

// -------- Password --------

/**
 * 更新数据库密码
 */
export function updateDatabasePassword(
	ref: string,
	data: UpdatePasswordBody
): Promise<ApiResponse<UpdatePasswordResponse>> {
	return request.patch<UpdatePasswordResponse>(
		`/v1/projects/${ref}/database/password`,
		data as Record<string, unknown>
	);
}

// -------- JIT Access --------

/**
 * 获取当前用户的 JIT 访问角色映射
 */
export function getJitAccess(
	ref: string
): Promise<ApiResponse<JitAccessResponse>> {
	return request.get<JitAccessResponse>(`/v1/projects/${ref}/database/jit`);
}

/**
 * 授权 JIT 访问
 */
export function authorizeJitAccess(
	ref: string,
	data: AuthorizeJitAccessBody
): Promise<ApiResponse<JitAuthorizeAccessResponse>> {
	return request.post<JitAuthorizeAccessResponse>(
		`/v1/projects/${ref}/database/jit`,
		data
	);
}

/**
 * 更新 JIT 访问
 */
export function updateJitAccess(
	ref: string,
	data: UpdateJitAccessBody
): Promise<ApiResponse<JitAccessResponse>> {
	return request.put<JitAccessResponse>(
		`/v1/projects/${ref}/database/jit`,
		data
	);
}

/**
 * 获取所有用户的 JIT 访问列表
 */
export function listJitAccess(
	ref: string
): Promise<ApiResponse<JitListAccessResponse>> {
	return request.get<JitListAccessResponse>(`/v1/projects/${ref}/database/jit/list`);
}

/**
 * 删除指定用户的 JIT 访问
 */
export function deleteJitAccess(
	ref: string,
	userId: string
): Promise<ApiResponse<Record<string, never>>> {
	return request.delete<Record<string, never>>(`/v1/projects/${ref}/database/jit/${userId}`);
}

// -------- Backups --------

/**
 * 获取所有备份列表
 */
export function listBackups(
	ref: string
): Promise<ApiResponse<BackupsResponse>> {
	return request.get<BackupsResponse>(`/v1/projects/${ref}/database/backups`);
}

/**
 * 恢复 PITR 备份
 */
export function restorePitrBackup(
	ref: string,
	data: RestorePitrBody
): Promise<ApiResponse<Record<string, never>>> {
	return request.post<Record<string, never>>(
		`/v1/projects/${ref}/database/backups/restore-pitr`,
		data
	);
}

/**
 * 创建还原点
 */
export function createRestorePoint(
	ref: string,
	data: RestorePointPostBody
): Promise<ApiResponse<RestorePointResponse>> {
	return request.post<RestorePointResponse>(
		`/v1/projects/${ref}/database/backups/restore-point`,
		data
	);
}

/**
 * 获取还原点
 */
export function getRestorePoint(
	ref: string,
	name?: string
): Promise<ApiResponse<RestorePointResponse>> {
	const params = name ? { name } : undefined;
	return request.get<RestorePointResponse>(
		`/v1/projects/${ref}/database/backups/restore-point`,
		params as Record<string, RequestParamValue>
	);
}

/**
 * 撤销到还原点
 */
export function undoToRestorePoint(
	ref: string,
	data: UndoBody
): Promise<ApiResponse<Record<string, never>>> {
	return request.post<Record<string, never>>(
		`/v1/projects/${ref}/database/backups/undo`,
		data
	);
}

// ============ 默认导出 ============

export default {
	// SQL Snippets
	listSnippets,
	getSnippet,
	// SSL Enforcement
	getSslEnforcement,
	updateSslEnforcement,
	// TypeScript Types
	generateTypescriptTypes,
	// Readonly Mode
	getReadOnlyStatus,
	disableReadOnlyTemporarily,
	// Read Replicas
	setupReadReplica,
	removeReadReplica,
	// Postgres Config
	getPostgresConfig,
	updatePostgresConfig,
	// PgBouncer Config
	getPgbouncerConfig,
	// Pooler (Supavisor) Config
	getPoolerConfig,
	updatePoolerConfig,
	// CLI Login Role
	createLoginRole,
	deleteLoginRoles,
	// Migrations
	listMigrations,
	applyMigration,
	upsertMigration,
	rollbackMigrations,
	getMigration,
	patchMigration,
	// Query
	runQuery,
	runReadOnlyQuery,
	// Webhooks
	enableDatabaseWebhook,
	// Database Context
	getDatabaseContext,
	// Password
	updateDatabasePassword,
	// JIT Access
	getJitAccess,
	authorizeJitAccess,
	updateJitAccess,
	listJitAccess,
	deleteJitAccess,
	// Backups
	listBackups,
	restorePitrBackup,
	createRestorePoint,
	getRestorePoint,
	undoToRestorePoint
};
